package com.dp.service.impl3;

import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.dp.pojo.JsonPage;
import com.dp.pojo.VideoContent;

import com.dp.pub.SysProperty;
import com.dp.pub.UtilsBean;
import com.dp.util.BookConfig;
import com.dp.util.DataMap;
import com.dp.util.ImageScalee;
import com.dp.util.InterUtil;
import com.dp.vo.TGenre;
import com.dp.vo.VideoContentinfo;
import com.dp.vo.VideoResource;

@Service
@Transactional
public class VideoServiceImpl3 extends BaseImpl {
	private static Logger log = Logger.getLogger(VideoServiceImpl3.class);
	
	public boolean insertVideoContent(VideoContentinfo v,String channelids,String mobFileResid,String mob_mp4FileResid,String webFileResid,
			String prepicFileResid,String fullpath)
	{
		UtilsBean b = new UtilsBean();
		//insert into video_contentinfo
		String contentid = b.ObjectToStringDB(v.getContentid());
		String vname = b.ObjectToStringDB(v.getVname());
		String servicecode = b.ObjectToStringDB(v.getServicecode());
		String intro = b.ObjectToStringDB(v.getIntro());
		String tag = b.ObjectToStringDB(v.getTag());
		String genreid = b.ObjectToStringDB(v.getGenreid());
		String language = b.ObjectToStringDB(v.getLanguage());
		String area = b.ObjectToStringDB(v.getArea());
		String copyright = b.ObjectToStringDB(v.getCopyright());
		String publishtime = "'"+b.getStrDate(v.getPublishtime())+"'";
		String userid = b.ObjectToStringDB(v.getUserid());
		Integer playtimes = v.getPlaytimes();
		String addtime = "now()";
		String status = b.ObjectToStringDB("new");
		String videosource = b.ObjectToStringDB(v.getVideosource());
		String content_updateddate = "now()";
		String cause = b.ObjectToStringDB(v.getCause());
		ArrayList<String> sqllist = new ArrayList<String>();
		StringBuffer inVcSql = new StringBuffer("insert into video_contentinfo values(");
		inVcSql.append(contentid+",");
		inVcSql.append(vname+",");
		inVcSql.append(servicecode+",");
		inVcSql.append(intro+",");
		inVcSql.append(tag+",");
		inVcSql.append(genreid+",");
		inVcSql.append(language+",");
		inVcSql.append(area+",");
		inVcSql.append(copyright+",");
		inVcSql.append(publishtime+",");
		inVcSql.append(userid+",");
		inVcSql.append(playtimes+",");
		inVcSql.append(addtime+",");
		inVcSql.append(status+",");
		inVcSql.append(videosource+",");
		inVcSql.append(content_updateddate+",");
		inVcSql.append(cause+")");
		sqllist.add(inVcSql.toString());
		inVcSql.delete(0, inVcSql.length());
		//insert into news_contenttitle
		String contenttext = "'"+v.getVname()+"" +
				"@"+InterUtil.rb.getString("src.source")+":"+v.getVideosource()+"" +
				"@"+InterUtil.rb.getString("src.update")+":"+b.getStrDate(new Date())+"" +
				"@"+InterUtil.rb.getString("src.intro")+":"+v.getIntro()+"" +
				"@"+InterUtil.rb.getString("src.duration")+":"+v.getPlaytimes()+""+InterUtil.rb.getString("src.second")+"" +
				"@"+InterUtil.rb.getString("src.language")+":"+v.getLanguage()+"" +
				"@"+InterUtil.rb.getString("src.label")+":"+v.getTag()+"'";
		//for wo magzine if(... is null) then content string=name+source+duration
		if("null".equals(language) && "null".equals(tag) && "null".equals(intro) && "null".equals(area) && "null".equals(copyright))
		{
			contenttext = "'"+v.getVname()+"@"+InterUtil.rb.getString("src.source")+":"+
			v.getVideosource()+"@"+InterUtil.rb.getString("src.duration")+":"+v.getPlaytimes()+""+InterUtil.rb.getString("src.second")+"'";
		}
		String contenticon = SysProperty.getPrep("sysiconid_title");
		if(contenticon == null || contenticon.equals(""))
        	contenticon = "1";
		inVcSql.append("insert into news_contenttitle (contentid,contenttitle,contenticon,contenttext,contentsource,contentsummary,newstype) values (");
		inVcSql.append(contentid+",");
		inVcSql.append(vname+",");
		inVcSql.append(contenticon+",");
		inVcSql.append(contenttext+",");
		inVcSql.append(videosource+",");
		String csummary="";
		if(v.getIntro()!=null){
			if(v.getIntro().length()>50){
				csummary=v.getIntro().substring(0, 50)+"...";
			}else{
				csummary=v.getIntro();
			}
		}
		inVcSql.append("'"+csummary+"',");
		inVcSql.append("'"+InterUtil.rb.getString("src.video")+"'"+")");
		sqllist.add(inVcSql.toString());
		//insert into news_extends_channel_title
		String [] channelid=channelids.split(",");
		String tempSql="insert into news_extends_channel_title(channleid,cid,contentstatus,istest,isnew)VALUES(";
		for(int i=0;i<channelid.length;i++)
        {
			//视频内容不需要测试,测试状态为1
			sqllist.add(tempSql+"(select channleid from news_extends_channel where channel_id='"+channelid[i]+"'),(select LAST_INSERT_ID()),0,1,1)");
        }
		//update video_resource
		tempSql = "update video_resource set video_contentid="+contentid+" where resourceid in ('"+mobFileResid+"','"+mob_mp4FileResid+"','"+webFileResid+"','"+prepicFileResid+"')";
		sqllist.add(tempSql);
		//insert into news_contenresource
		if(fullpath != null && fullpath.length()>0)
		{
			tempSql = "insert into news_contenresource values ((select LAST_INSERT_ID()),(select rid from news_resources where fullpath='"+fullpath+"'))";
			sqllist.add(tempSql);
		}
		try {
			return execTransaction(sqllist);
		} catch (Exception e) {
			log.info(" insertVideoContent fail !!!" +e.getMessage());
			e.printStackTrace();
			return false;
		}
	}
	
	public ArrayList findAllLastChannel(Object roleId, String userId) {
		//root
		String findSql = "select  ch.channel_id  ,ch.service_code,ch.channel_name,ch.channel_comment ,ch.function_name,ch.STATUS ,ch.ordered ,ch.created_date,ch.previous_id ,ch.primal_id ,ch.LEVEL ,ch.channel_type  ,ch.channel_hot_topics ,ch.channel_classifying_label" +"  from t_channels ch WHERE 1=1 " +
				//will open
				" and status='"+BookConfig.channel_status_enable+"'" +
				" and channel_type='"+BookConfig.channel_type_video+"' " + 
				" AND (function_name='LAST' OR function_name='CHANNELSONG') ";
		//normal user
		if(roleId != null && !roleId.equals(""))
		{
			findSql = "select  ch.channel_id  ,ch.service_code,ch.channel_name,ch.channel_comment ,ch.function_name,ch.STATUS ,ch.ordered ,ch.created_date,ch.previous_id ,ch.primal_id ,ch.LEVEL ,ch.channel_type  ,ch.channel_hot_topics ,ch.channel_classifying_label" +"  from t_channels ch,t_user_channels uc WHERE ch.channel_id=uc.channel_id " +
		    		" and status='"+BookConfig.channel_status_enable+"'" +
		    		" and channel_type='"+BookConfig.channel_type_video+"' " + 
		    		" and (function_name='"+BookConfig.channel_last+"' OR function_name='"+BookConfig.channel_song+"')  " +
		    		" and uc.employee_id=(SELECT employee_id FROM t_user where user_id='" + userId + "')";
		}
		try {
			return queryALLResultSet(findSql.toString());
		} catch (Exception e) {
			log.info(" findAllLastChannel fail !!!" +e.getMessage());
			return null;
		}
	}
	
	/**
	 * 用户可支配的栏目
	 * @param hashMap
	 * @return
	 */
	public ArrayList<DataMap> findAllLastChannel(Object roleId, String userId,String userids) {
		//root
		String findSql = "select  ch.channel_id  ,ch.service_code,ch.channel_name,ch.channel_comment ,ch.function_name,ch.STATUS ,ch.ordered ,ch.created_date,ch.previous_id ,ch.primal_id ,ch.LEVEL ,ch.channel_type  ,ch.channel_hot_topics ,ch.channel_classifying_label" +"  from t_channels ch WHERE 1=1 " +
				//will open
				//" and status='"+BookConfig.channel_status_enable+"'" +
				" and channel_type='"+BookConfig.channel_type_video+"' " + 
				" AND (function_name='"+BookConfig.channel_last+"' OR function_name='"+BookConfig.channel_song+"') ";
		//normal user
		if(roleId != null && !roleId.equals(""))
		{
			findSql = "select  ch.channel_id  ,ch.service_code,ch.channel_name,ch.channel_comment ,ch.function_name,ch.STATUS ,ch.ordered ,ch.created_date,ch.previous_id ,ch.primal_id ,ch.LEVEL ,ch.channel_type  ,ch.channel_hot_topics ,ch.channel_classifying_label" +"  from t_channels ch,t_user_channels uc WHERE ch.channel_id=uc.channel_id " +
		    		//" and status='"+BookConfig.channel_status_enable+"'" +
		    		" and channel_type='"+BookConfig.channel_type_video+"' " + 
		    		" and (function_name='"+BookConfig.channel_last+"' OR function_name='"+BookConfig.channel_song+"')  " +
		    		" and uc.employee_id=(SELECT employee_id FROM t_user where user_id='" + userId + "')";
		}else if(userids!=null){
			findSql = "select distinct  ch.channel_id  ,ch.service_code,ch.channel_name,ch.channel_comment ,ch.function_name,ch.STATUS ,ch.ordered ,ch.created_date,ch.previous_id ,ch.primal_id ,ch.LEVEL ,ch.channel_type  ,ch.channel_hot_topics ,ch.channel_classifying_label" +"  from t_channels ch,t_user_channels uc WHERE ch.channel_id=uc.channel_id " +
	    		//" and status='"+BookConfig.channel_status_enable+"'" +
	    		" and channel_type='"+BookConfig.channel_type_video+"' " + 
	    		" and (function_name='"+BookConfig.channel_last+"' OR function_name='"+BookConfig.channel_song+"')  " +
	    		" and uc.employee_id in (SELECT employee_id FROM t_user where user_id in (" + userids + "))";
		}
		
		try {
			return this.queryALLResultSet(findSql.toString());
		} catch (Exception e) {
			logger.error(" findAllLastChannel fail !!!" +e.getMessage());
			return null;
		}
	}
	
	public VideoContentinfo getVideoById(String contentid)
	{
		String hql = "SELECT vc FROM VideoContentinfo vc where 1=1" ;
		if (contentid != null && !contentid.equals("")) {
			hql += " and vc.contentid = '" + contentid +"'";
		}
		Object obj = getSingleResult(hql);
		if(obj == null ){
			return null;
		}else{
			return (VideoContentinfo) obj;
		}
	}
	
	public VideoResource getMobResByContentid(String contentid)
	{
		String hql = "SELECT vr FROM VideoResource vr where vr.useby='mob'" ;
		if (contentid != null && !contentid.equals("")) {
			hql += " and vr.videoContentid = '" + contentid +"'";
		}
		Object obj = getSingleResult(hql);
		if(obj == null ){
			return null;
		}else{
			return (VideoResource) obj;
		}
	}
	
	public VideoResource getMob_mp4ResByContentid(String contentid)
	{
		String hql = "SELECT vr FROM VideoResource vr where vr.useby='mp4'" ;
		if (contentid != null && !contentid.equals("")) {
			hql += " and vr.videoContentid = '" + contentid +"'";
		}
		Object obj = getSingleResult(hql);
		if(obj == null ){
			return null;
		}else{
			return (VideoResource) obj;
		}
	}
	
	public VideoResource getWebResByContentid(String contentid)
	{
		String hql = "SELECT vr FROM VideoResource vr where vr.useby='web'" ;
		if (contentid != null && !contentid.equals("")) {
			hql += " and vr.videoContentid = '" + contentid +"'";
		}
		Object obj = getSingleResult(hql);
		if(obj == null ){
			return null;
		}else{
			return (VideoResource) obj;
		}
	}
	public VideoResource getPrepicResByContentid(String contentid)
	{
		String hql = "SELECT vr FROM VideoResource vr where vr.ctype='1'" ;
		if (contentid != null && !contentid.equals("")) {
			hql += " and vr.videoContentid = '" + contentid +"'";
		}
		Object obj = getSingleResult(hql);
		if(obj == null ){
			return null;
		}else{
			return (VideoResource) obj;
		}
	}
	
	public List<TGenre> getGenreAll()
	{
		String hql = "SELECT tg FROM TGenre tg " ;
		//open
			hql+=	"where tg.conentType='9'" ;
		return getQueryList(hql);
	}
	
	public ArrayList getServiceAll(String roleid,String userid)
	{
		String sql = "";
        
        if(roleid != null && !roleid.equals("")||roleid==null&&!"root".equals(userid))
        {
	        sql += "SELECT p.serviceid,p.servicename,p.service_code,p.price,p.price_type,p.servicetype,p.access_no,p.port_type,p.content,p.content_type,p.processClass,p.carrier,p.spnumber,p.order_success_msg,p.ctrmand_cmd,p.ctrmand_no,p.starttime" +
	        		" FROM t_service_info p,t_user_prices up,t_user tu where p.service_code=up.service_code and " +
				"up.employee_id=tu.employee_id and tu.user_id='"+userid+"'";
        }
        else if(roleid == null || roleid.equals(""))
        {
        	sql = "select p.serviceid,p.servicename,p.service_code,p.price,p.price_type,p.servicetype,p.access_no,p.port_type,p.content,p.content_type,p.processClass,p.carrier,p.spnumber,p.order_success_msg,p.ctrmand_cmd,p.ctrmand_no,p.starttime" +
        			" from t_service_info p ";
        }
        try {
            return queryALLResultSet(sql);
        } catch (Exception e) {
        	log.info(" getPriceAll fail !!!" +e.getMessage());
            return null;
        }
	}
	public ArrayList getServiceAll(String roleid,String userid,String userids)
	{
		String sql = "";
        
        if(roleid != null && !roleid.equals(""))
        {
	        sql += "SELECT p.serviceid,p.servicename,p.service_code,p.price,p.price_type,p.servicetype,p.access_no,p.port_type,p.content,p.content_type,p.processClass,p.carrier,p.spnumber,p.order_success_msg,p.ctrmand_cmd,p.ctrmand_no,p.starttime" +
	        		" FROM t_service_info p,t_user_prices up,t_user tu where p.service_code=up.service_code and " +
				"up.employee_id=tu.employee_id and tu.user_id='"+userid+"'";
        }else if(userids!=null){
        	sql = "SELECT distinct p.serviceid,p.servicename,p.service_code,p.price,p.price_type,p.servicetype,p.access_no,p.port_type,p.content,p.content_type,p.processClass,p.carrier,p.spnumber,p.order_success_msg,p.ctrmand_cmd,p.ctrmand_no,p.starttime" +
        			" FROM t_service_info p,t_user_prices up,t_user tu where p.service_code=up.service_code and  ";
        	sql = sql + " up.employee_id in (SELECT employee_id FROM t_user where user_id in (" + userids + "))";
        }
        else if(roleid == null || roleid.equals(""))
        {
        	sql = "select p.serviceid,p.servicename,p.service_code,p.price,p.price_type,p.servicetype,p.access_no,p.port_type,p.content,p.content_type,p.processClass,p.carrier,p.spnumber,p.order_success_msg,p.ctrmand_cmd,p.ctrmand_no,p.starttime" +
        			" from t_service_info p ";
        }
        try {
            return queryALLResultSet(sql);
        } catch (Exception e) {
        	log.info(" getPriceAll fail !!!" +e.getMessage());
            return null;
        }
	}
	
	public String getSpcodeByUserId(String userid)
	{
		String sql = "select te.sp_code from t_employee te, t_user tu where tu.employee_id=te.employee_id and tu.user_id='" + userid + "'";
		String s = "";
		try {
			s = queryOne(sql);
		} catch (Exception e) {
			log.info(" getSpcodeByUserId excute sql : " +sql);
			log.info(" getSpcodeByUserId fail !!!" +e.getMessage());
			e.printStackTrace();
			return "nospcode";
		}
		return s;
	}
	
	public List<VideoContent> findAllVideoContent(boolean audit,String roleid,String userid, String vname,String genreid,String status,String searchChid,
			int firstrow, int maxrow, JsonPage jpage, String userids) {
		String sql = "SELECT tg.genre_name,p.price,vc.contentid,vc.vname,vc.servicecode,vc.intro,vc.tag,vc.genreid,vc.language,vc.area,vc.copyright,vc.publishtime,vc.userid,vc.playtimes,vc.addtime,vc.status,vc.videosource,vc.content_updateddate,vc.cause" +
				" FROM t_service_info p,video_contentinfo vc left join t_genre tg on tg.genre_id=vc.genreid";
		if(searchChid!=null&&searchChid.trim().length()>0) {
			sql += ",news_extends_channel_title nect,news_extends_channel nec,news_contenttitle nc ";
			sql += "where nect.channleid=nec.channleid and nec.channel_id='"+searchChid+"' and nect.cid=nc.cid and nc.contentid=vc.contentid " +
					"and p.service_code=vc.servicecode";
		}
		else
			sql += " where p.service_code=vc.servicecode";
		if (vname != null && !"".equals(vname)) {
			sql += " and vc.vname like '%" + vname + "%'";
		}
		if (genreid != null && !"".equals(genreid)) {
			sql += " and vc.genreid = '" + genreid + "'";
		}
		if(status != null && !"".equals(status)) {
			sql += " and vc.status = '" + status + "'";
		}
		if(roleid != null && !"".equals(roleid)) {
			sql += " and vc.userid = '" + userid + "'";
		}else if(userids!=null){
			sql += " and vc.userid in ("+ userids +")";
		}
		if(audit)
		{
			sql += " and vc.status = 'auditing'";
		}
		if(jpage!=null){
			if(jpage.getSidx()!=null&&!"".equals(jpage.getSidx())){
				sql+=" order by vc."+jpage.getSidx()+" "+jpage.getSord();
			}
		}
		log.info(" findAllVideoContent excute sql : " +sql);
		List<Object[]> list = this.getNativeQuery(sql, firstrow, maxrow);
		List<VideoContent> result = null;
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		if (list.size() > 0) 
		{
			result = new ArrayList<VideoContent>(list.size());
			for (Object[] objects : list) 
			{
				if (objects != null && objects.length > 0) 
				{
					int j = 0;
					VideoContent v = new VideoContent();
					v.setGenrename((String)objects[j++]);
					v.setPrice((Integer)objects[j++]);
					v.setContentid((String)objects[j++]);
					v.setVname((String)objects[j++]);
					v.setServicecode((String)objects[j++]);
					v.setIntro((String)objects[j++]);
					v.setTag((String)objects[j++]);
					v.setGenreid((String)objects[j++]);
					v.setLanguage((String)objects[j++]);
					v.setArea((String)objects[j++]);
					v.setCopyright((String)objects[j++]);
					v.setPublishtime(sdf.format((Date)objects[j++]));
					v.setUserid((String)objects[j++]);
					v.setPlaytimes((Integer)objects[j++]);
					v.setAddtime(sdf.format((Date)objects[j++]));
					v.setStatus((String)objects[j++]);
					v.setVideosource((String)objects[j++]);
					v.setContent_updateddate(sdf.format((Date)objects[j++]));
					v.setCause((String)objects[j++]);
					result.add(v);
				}
			}
		}
		return result;
	}
	
	public int findAllVideoContentCount(boolean audit,String roleid,String userid, String vname,String genreid,String status,String searchChid, String userids) {
		sql = "SELECT count(vc.contentid) FROM t_service_info p,video_contentinfo vc left join t_genre tg on tg.genre_id=vc.genreid";
		if(searchChid!=null&&searchChid.trim().length()>0) {
			sql += ",news_extends_channel_title nect,news_extends_channel nec,news_contenttitle nc ";
			sql += "where nect.channleid=nec.channleid and nec.channel_id='"+searchChid+"' and nect.cid=nc.cid and nc.contentid=vc.contentid " +
					"and p.service_code=vc.servicecode ";
		}
		else
			sql += " where p.service_code=vc.servicecode";
		if (vname != null && !"".equals(vname)) {
			sql += " and vc.vname like '%" + vname + "%'";
		}
		if (genreid != null && !"".equals(genreid)) {
			sql += " and vc.genreid = '" + genreid + "'";
		}
		if(status != null && !"".equals(status)) {
			sql += " and vc.status = '" + status + "'";
		}
		if(roleid != null && !"".equals(roleid)) {
			sql += " and vc.userid = '" + userid + "'";
		}else if(userids!=null){
			sql += " and vc.userid in ("+ userids +")";
		}
		if(audit)
		{
			sql += " and vc.status = 'auditing'";
		}
		int count =0;
		try {
			count =this.getOneInt();
		} catch (Exception e) {
			log.info(" findAllVideoContentCount fail !!!" +e.getMessage());
			e.printStackTrace();
		}
		return count;
	}
	
	/* (non-Javadoc)
	 * @see com.dp.service.NewsService#findSelectChannelsById(java.lang.String)
	 */
	public ArrayList findSelectChannelsById(String content_id) {
		StringBuffer findSql = new StringBuffer("");
		findSql.append("select tc.channel_id,tc.channel_name from t_channels tc,news_extends_channel nec,news_extends_channel_title nct,news_contenttitle nc where 1=1 ");
		findSql.append(" and nc.contentid='" + content_id + "'");
		findSql.append(" and nc.cid=nct.cid ");
		findSql.append(" and nec.channel_id=tc.channel_id ");
		findSql.append(" and nec.channleid=nct.channleid ");
		//will open
//		findSql.append(" and status='"+BookConfig.channel_status_enable+"' ");
//		findSql.append(" AND (tc.function_name='"+BookConfig.channel_last+"' OR tc.function_name='"+BookConfig.channel_song+"') ");
        try {
			return queryALLResultSet(findSql.toString());
		} catch (Exception e) {
			log.info(" findSelectChannelsById fail !!!" +e.getMessage());
            return null;
		}
	}
	
	/* (non-Javadoc)
	 * @see com.dp.service.NewsService#findNotChooseChannel(java.lang.String)
	 */
	public ArrayList findNotChooseChannel(String content_id,Object roleId,String userId,String userids) {
		//root
		String findSql = "select tc.channel_id,tc.channel_name from t_channels tc WHERE 1=1 " +
				//will open
				" and status='"+BookConfig.channel_status_enable+"' " +
				" and channel_type='"+BookConfig.channel_type_video+"' " + 
				" AND (tc.function_name='LAST' OR tc.function_name='CHANNELSONG') ";
		//normal user
		if(roleId != null && !roleId.equals(""))
        {
			findSql = "select tc.channel_id,tc.channel_name from t_channels tc,t_user_channels uc WHERE tc.channel_id=uc.channel_id " +
            		" and status='"+BookConfig.channel_status_enable+"'" +
            		" and channel_type='"+BookConfig.channel_type_video+"' " + 
            		" and (function_name='"+BookConfig.channel_last+"' OR function_name='"+BookConfig.channel_song+"')  " +
            		" and uc.employee_id=(SELECT employee_id FROM t_user where user_id='" + userId + "')";
        }else if(userids!=null){
        	findSql = "select distinct tc.channel_id,tc.channel_name from t_channels tc,t_user_channels uc WHERE tc.channel_id=uc.channel_id " +
    		" and status='"+BookConfig.channel_status_enable+"'" +
    		" and channel_type='"+BookConfig.channel_type_video+"' " + 
    		" and (function_name='"+BookConfig.channel_last+"' OR function_name='"+BookConfig.channel_song+"')  " +
    		" and uc.employee_id in (SELECT employee_id FROM t_user where user_id in (" + userids + "))";
        }
		findSql = findSql + " and tc.channel_id not in (select tc.channel_id from t_channels tc,news_extends_channel nec," +
				"news_extends_channel_title nct,news_contenttitle nc where nc.contentid='"+content_id+"' and nc.cid=nct.cid and " +
						"nec.channel_id=tc.channel_id and nec.channleid=nct.channleid )";
		
		try {
			return queryALLResultSet(findSql.toString());
		} catch (Exception e) {
			log.info(" findNotChooseChannel fail !!!" +e.getMessage());
			return null;
		}
	}
	
	/*public boolean updateResDuration(String mobid,String webid,int duration)
	{
		String sql = "update video_resource set duration="+duration+" where resourceid in('"+mobid+"',"+"'"+webid+"')";
		try {
			return execUpdate(sql);
		} catch (Exception e) {
			log.info(" updateResDuration fail !!!" +e.getMessage());
			e.printStackTrace();
			return false;
		}
	}*/
	
	public boolean updateVideoContent(VideoContentinfo v,String channelids,String mobFileResid,String mob_mp4FileResid,
			String webFileResid,String prepicFileResid,String fullpath)
	{
		UtilsBean b = new UtilsBean();
		//insert into video_contentinfo
		String contentid = b.ObjectToStringDB(v.getContentid());
		String vname = b.ObjectToStringDB(v.getVname());
		String servicecode = b.ObjectToStringDB(v.getServicecode());
		String intro = b.ObjectToStringDB(v.getIntro());
		String tag = b.ObjectToStringDB(v.getTag());
		String genreid = b.ObjectToStringDB(v.getGenreid());
		String language = b.ObjectToStringDB(v.getLanguage());
		String area = b.ObjectToStringDB(v.getArea());
		String copyright = b.ObjectToStringDB(v.getCopyright());
		String publishtime = "'"+b.getStrDate(v.getPublishtime())+"'";
		String userid = b.ObjectToStringDB(v.getUserid());
		Integer playtimes = v.getPlaytimes();
//		String addtime = "'"+b.getStrDate(v.getAddtime())+"'";
		String status = b.ObjectToStringDB("new");
		String videosource = b.ObjectToStringDB(v.getVideosource());
		ArrayList<String> sqllist = new ArrayList<String>();
		StringBuffer inVcSql = new StringBuffer("update video_contentinfo set ");
		inVcSql.append("vname="+vname+",");
		inVcSql.append("servicecode="+servicecode+",");
		inVcSql.append("intro="+intro+",");
		inVcSql.append("tag="+tag+",");
		inVcSql.append("genreid="+genreid+",");
		inVcSql.append("language="+language+",");
		inVcSql.append("area="+area+",");
		inVcSql.append("copyright="+copyright+",");
		inVcSql.append("publishtime="+publishtime+",");
		//不更新当前操作用户为内容创建人
		//inVcSql.append("userid="+userid+",");
		inVcSql.append("playtimes="+playtimes+",");
//		inVcSql.append("addtime="+addtime+",");
		inVcSql.append("status="+status+",");
		inVcSql.append("videosource="+videosource+",content_updateddate=now() where contentid="+contentid);
		System.out.println(inVcSql);
		sqllist.add(inVcSql.toString());
		inVcSql.delete(0, inVcSql.length());
		//insert into news_contenttitle
		
		String contenttext = "'"+v.getVname()+"" +
				"@"+InterUtil.rb.getString("src.source")+":"+v.getVideosource()+"" +
				"@"+InterUtil.rb.getString("src.update")+":"+b.getStrDate(new Date())+"" +
				"@"+InterUtil.rb.getString("src.intro")+":"+v.getIntro()+"" +
				"@"+InterUtil.rb.getString("src.duration")+":"+v.getPlaytimes()+""+InterUtil.rb.getString("src.second")+"" +
				"@"+InterUtil.rb.getString("src.language")+":"+v.getLanguage()+"" +
				"@"+InterUtil.rb.getString("src.label")+":"+v.getTag()+"'";
		
		//for wo magzine if(... is null) then content string=name+source+duration
		if("null".equals(language) && "null".equals(tag) && "null".equals(intro) && "null".equals(area) && "null".equals(copyright))
		{
			contenttext = "'"+v.getVname()+"@"+InterUtil.rb.getString("src.source")+":"+
			v.getVideosource()+"@"+InterUtil.rb.getString("src.duration")+":"+v.getPlaytimes()+""+InterUtil.rb.getString("src.second")+"'";
		}
		inVcSql.append("update news_contenttitle set ");
		inVcSql.append("contenttitle="+vname+",");
//		inVcSql.append("1,");
		inVcSql.append("contenttext="+contenttext+",");
		String csummary="";
		if(v.getIntro()!=null){
			if(v.getIntro().length()>50){
				csummary=v.getIntro().substring(0, 50)+"...";
			}else{
				csummary=v.getIntro();
			}
		}
		inVcSql.append("contentsummary='"+csummary+"',");
		inVcSql.append("contentsource="+videosource+",updatetime=now() where contentid="+contentid);
		System.out.println(inVcSql);
		sqllist.add(inVcSql.toString());
		inVcSql.delete(0, inVcSql.length());
		//delete old channel content
		inVcSql.append("delete from news_extends_channel_title where cid=(SELECT cid FROM news_contenttitle where contentid="+contentid+")");
		sqllist.add(inVcSql.toString());
		//insert into news_extends_channel_title
		String [] channelid=channelids.split(",");
		String tempSql="insert into news_extends_channel_title(channleid,cid,contentstatus,istest,isnew)VALUES(";
		for(int i=0;i<channelid.length;i++)
        {
			//视频内容不需要测试,测试状态为1
			sqllist.add(tempSql+"(select channleid from news_extends_channel where channel_id='"+channelid[i]+"')," +
        			"(SELECT cid FROM news_contenttitle where contentid="+contentid+"),0,1,1)");
        }
		//update video_resource duration
		tempSql = "update video_resource set duration="+playtimes+",video_contentid="+contentid+" where resourceid in ('"+mobFileResid+"','"+mob_mp4FileResid+"','"+webFileResid+"','"+prepicFileResid+"')";
		sqllist.add(tempSql);
		if(fullpath != null && fullpath.length()>0)
		{
			tempSql = "delete from news_contenresource where cid=(SELECT cid FROM news_contenttitle where contentid="+contentid+")";
			sqllist.add(tempSql);
			tempSql = "insert into news_contenresource values ((SELECT cid FROM news_contenttitle where contentid="+contentid+")," +
					"(select max(rid) from news_resources where fullpath='"+fullpath+"'))";
			sqllist.add(tempSql);
		}
		try {
			return execTransaction(sqllist);
		} catch (Exception e) {
			log.info(" updateVideoContent fail !!!" +e.getMessage());
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean subAuditVideo(String content_id_list)
	{
		String updateSql = "";
		if(content_id_list!=null && !content_id_list.equals(""))
		{
			updateSql = "update video_contentinfo set status='auditing', content_updateddate=now() where contentid in ("+content_id_list+")";
			try {
				return execUpdate(updateSql);
			} catch (Exception e) {
				log.info(" subAuditVideo fail !!!" +e.getMessage());
				e.printStackTrace();
				return false;
			}
		}
		else
			return true;
	}
	
	public boolean unpassVideo(String content_id_list,String cause)
	{
		String updateSql = "";
		if(content_id_list!=null && !content_id_list.equals(""))
		{
			updateSql = "update video_contentinfo set status='unpass', cause='"+cause+"',content_updateddate=now() where contentid in ("+content_id_list+")";
			try {
				return execUpdate(updateSql);
			} catch (Exception e) {
				log.info(" unpassVideo fail !!!" +e.getMessage());
				e.printStackTrace();
				return false;
			}
		}
		else
			return true;
	}
	
	public boolean auditVideo(String content_id_list)
	{
		
		//对应的栏目list
		ArrayList chList = null;
		//从复选框取得contentidlist
		ArrayList sqlList = new ArrayList();
		String updateSql = "";
		if(content_id_list!=null && !content_id_list.equals(""))
		{
			updateSql = "update video_contentinfo set status='pass', cause=null,content_updateddate=now() where contentid in ("+content_id_list+")";
			sqlList.add(updateSql);
			String[] c_id_strList=content_id_list.split(",");
			try {
	        	for(String contentId:c_id_strList)
	        	{
					//修改前需要检查对应栏目的更新方式,0表示实时更新,发布状态为1,其他值为计时器,状态值为2
					//查询栏目的更新方式
	        		//modify 0928:不需要判断栏目的更新方式
//					chList = db.queryALLResultSet("SELECT nec.channleid,nec.updatetimer,nc.cid FROM news_extends_channel nec," +
//						"news_extends_channel_title ncc,news_contenttitle nc where nc.contentid= "+contentId+" and " +
//								"nec.channleid=ncc.channleid and ncc.cid=nc.cid ");
//					for(int i = 0;i<chList.size();i++)
//					{
//						DataMap chmap = (DataMap)chList.get(i);
//						String chid = (String)chmap.get("channleid");
//						String chup = (String)chmap.get("updatetimer");
//						String cid = (String)chmap.get("cid");
//						String tmpsql = "";
//						if(chup.equals("0"))
//							tmpsql = "update news_extends_channel_title set contentstatus=1 where channleid='"+chid+"' and cid='"+cid+"'";
//						else
//							tmpsql = "update news_extends_channel_title set contentstatus=2 where channleid='"+chid+"' and cid='"+cid+"'";
//							sqlList.add(tmpsql);
//					}
	        		//审核时生成内容icon
	        		String resourceid = this.scaleImage(contentId);
	        		if(resourceid == null)
		            	return false;
	        		else if(resourceid!=null && !resourceid.equals(""))
		            {
	        			String iconrid=this.queryOne("select max(n.rid) rid from news_resources n where n.name='"+resourceid+"'");
		            	String upConIconSql = "update news_contenttitle set contenticon="+iconrid+" where contentid="+contentId;
		            	sqlList.add(upConIconSql);
		            }
	        		Object cid = this.getSingleResult("select cid from NewsContenttitle where contentid="+contentId);
					String tmpsql = "update news_extends_channel_title set contentstatus=2 where cid=" +cid;
					sqlList.add(tmpsql);
				} 
	        	return this.execTransaction(sqlList);
			} catch (Exception e) {
				log.info(" udpateAuditNews fail !!!" +e.getMessage());
				e.printStackTrace();
				return false;
			}
		}
		else
			return true;
	}
	
	public String scaleImage(String contentid)
	{
		String sql = "select vr.resourceid,vr.fullpath from video_resource vr where vr.ctype='1' and vr.video_contentid="+contentid;
		List resList = null;
		String resourceid = "";
		try {
			resList = this.queryALLResultSet(sql);
		
			if(resList.size()>0)
			{
				HashMap<String,String> tmpMap = (HashMap)resList.get(0);
				resourceid = tmpMap.get("resourceid");
				String fullpath = tmpMap.get("fullpath");
				ImageScalee ims = new ImageScalee();
				String imageSizeScaleStr = SysProperty.getPrep("news_imagesizescale");
	        	String imageFormatScaleStr = SysProperty.getPrep("news_imageformatscale");
	        	if(imageFormatScaleStr == null || imageFormatScaleStr.equals(""))
	        		imageFormatScaleStr = "png";
				String[] imageSizeScaleAry = imageSizeScaleStr.split(","); 
				String[] imageFormatScaleAry = imageFormatScaleStr.split(","); 
				if(imageSizeScaleAry != null&&imageSizeScaleAry.length>0)
				{
					for(String imageSizeScale:imageSizeScaleAry)
					{
						String screenWidHei = imageSizeScale.split("@")[0];
						String picWidHei = imageSizeScale.split("@")[1];
						int screenWidth = Integer.parseInt(screenWidHei.split("x")[0]);
						int screenHeight = Integer.parseInt(screenWidHei.split("x")[1]);
						int width = Integer.parseInt(picWidHei.split("x")[0]);
						int height = Integer.parseInt(picWidHei.split("x")[1]);
						if(imageFormatScaleAry!=null && imageFormatScaleAry.length>0)
							for(String imageFormatScale:imageFormatScaleAry)
								ims.compressPicsScale(fullpath, width, height, imageFormatScale, screenWidth, screenHeight);
					}
				}
				String scaleFullPath = fullpath.replace(".", "-p.");
				sql = "insert into news_resources (name,newformat,fullpath) values('"+resourceid+"','"+imageFormatScaleStr+"','"+scaleFullPath+"')";
				this.execUpdate(sql);
			}
		} catch (Exception e) {
			log.info(" parse content icon fail !!!" +e.getMessage());
			e.printStackTrace();
			return null;
		}
		return resourceid;
	}
	
	public boolean disableVideo(String contentid)
	{
		ArrayList<String> sqlList = new ArrayList<String>();
		String sql = "update video_contentinfo set status='disable' where contentid ='"+contentid+"'";
		sqlList.add(sql);
		sql = "update news_extends_channel_title set contentstatus=3 where cid=" +
				"(SELECT cid FROM news_contenttitle where contentid='"+contentid+"')";
		sqlList.add(sql);
		try {
			return execTransaction(sqlList);
		} catch (Exception e) {
			log.info(" disable video fail !!!" +e.getMessage());
			e.printStackTrace();
			return false;
		}
	}
	
	public List getChannelsbyVideo(String contentid)
	{
		String sql = "select tc.channel_id,tc.channel_name from news_contenttitle nc,t_channels tc,news_extends_channel_title nect,news_extends_channel nec " +
		"where nc.contentid='"+contentid+"' and nc.cid=nect.cid and nect.channleid=nec.channleid and tc.channel_id=nec.channel_id";
		try {
			return queryALLResultSet(sql);
		} catch (Exception e) {
			log.info(" getChannelsbyVideo fail !!!" +e.getMessage());
			return null;
		}
	}
	
	 //add 1119 select channel update
    public DataMap buildLastChannelMap()
    {
    	String sql = "SELECT channel_id,channel_name,function_name,previous_id FROM t_channels t";
    	HashMap<String,String> childIdAndPreiousId = new HashMap<String,String>();
    	HashMap<String,String> idAndName = new HashMap<String,String>();
    	DataMap result = new DataMap();
    	List chList = null;
    	try {
			chList = queryALLResultSet(sql);
		} catch (Exception e) {
			log.info(" java bean buildChChildIdAndPreviousIdMap fail !!!" +e.getMessage());
			e.printStackTrace();
			return null;
		}
		//首先建立两个map准备数据
		for(int i=0;i<chList.size();i++)
		{
			HashMap hashMap = (HashMap)chList.get(i);
			String channel_id = (String)hashMap.get("channel_id");
			String chName = (String)hashMap.get("channel_name");
			String fnName = (String)hashMap.get("function_name");
			String previous_id = (String)hashMap.get("previous_id");
			//先将id和name装入map
			idAndName.put(channel_id, chName);
			//channel如果是normal或者first,就装入map,key是channel_id,value是
			if(fnName.equals(BookConfig.channel_first) || fnName.equals(BookConfig.channel_normal))
			{
				childIdAndPreiousId.put(channel_id, previous_id);
			}
		}
		//build map,key:channleid value:previousid+name
		for(int i=0;i<chList.size();i++)
		{
			HashMap hashMap = (HashMap)chList.get(i);
			String channel_id = (String)hashMap.get("channel_id");
			String chName = (String)hashMap.get("channel_name");
			String fnName = (String)hashMap.get("function_name");
			String previous_id = (String)hashMap.get("previous_id");
			//如果是normal或者first,就装入map,key是channel_id,value是
			if(fnName.equals(BookConfig.channel_last) || fnName.equals(BookConfig.channel_song))
			{
				String previousChName = idAndName.get(previous_id);
				chName = chName+"="+previousChName;
				chName = getValue(childIdAndPreiousId,idAndName,previous_id,chName);
				//最上一级的频道名称必然是news,把news过滤掉
				chName = chName.substring(0,chName.lastIndexOf("="));
				String[] tmp = chName.split("=");
				String value = "";
				for(int j=tmp.length-1;j>=0;j--)
				{
					value=value+tmp[j]+"--";
				}
				value = value.substring(0,value.length()-2);
				result.put(channel_id, value);
			}
		}
    	return result;
    }
    
    private String getValue(HashMap<String,String> idmap,HashMap<String,String> namemap,String key,String result)
    {
    	String parentid = idmap.get(key);
    	String parentname = namemap.get(parentid);
    	if((key != null && parentid!=null) && !parentid.equals(key))
    	{
    		result = result+"="+parentname;
    		return getValue(idmap,namemap,parentid,result);
    	}
    	else
    	{
    		return result;
    	}
    }

	 //add for multi catalog
    public ArrayList getChannelPlatform()
    {
    	ArrayList result = new ArrayList();
    	//1 取得所有生效的catalog对应的catalogchannelid
    	String sql = "SELECT ncc.channelid,ncp.mplatform,ncp.magent,ncp.mserialnum,ncp.mlocale,ncp.catlogid FROM news_catlog nc left join news_catlog_platform ncp on ncp.catlogid=nc.catlogid," +
    			"news_catlog_channel ncc where nc.isnowpublish in (1,2) and nc.catlogid=ncc.catlogid";
    	ArrayList catalogChannelidAndPlatformList = new ArrayList();
    	try {
    		catalogChannelidAndPlatformList = queryALLResultSet(sql);
    		if(catalogChannelidAndPlatformList.size()>0)
    		{
	    		for(int i=0;i<catalogChannelidAndPlatformList.size();i++)
	    		{
	    			DataMap lastChannelAndPlatform = new DataMap();
	    			HashMap hashmap = (HashMap)catalogChannelidAndPlatformList.get(i);
	    			String catalogChannelid = (String)hashmap.get("channelid");
	    			String platforminfo = InterUtil.rb.getString("src.gameplatform")+hashmap.get("mplatform")+"."+
	    			InterUtil.rb.getString("src.gamedistributor")+hashmap.get("magent")+"."+InterUtil.rb.getString("src.clientsn")+
	    			hashmap.get("mserialnum")+"."+InterUtil.rb.getString("src.languagearea")+hashmap.get("mlocale");
	    			sql = "SELECT nc.channel_id FROM news_buttons nb,news_extends_firstchannel_buttons nfb,news_contenttype nc where " +
	    					"nfb.channelid="+catalogChannelid+" and nc.newscontenttype=nb.buttoncontenttype and nfb.buttonid=nb.buttonid";
	    			ArrayList tchannelIdList = queryALLResultSet(sql);
	    			if(tchannelIdList.size()>0)
	    			{
	    				//首先要判断结果集里的channel是否最后一级
	    				for(int j=0;j<tchannelIdList.size();j++)
	    				{
	    					DataMap tmpMap = (DataMap)tchannelIdList.get(j);
	    					String tch_id = tmpMap.getMapData("channel_id");
	    					if(tch_id == null || tch_id.equals(""))
	    						continue;
	    					sql = "select function_name from t_channels where channel_id='"+tch_id+"'";
	    					String functionname = queryOne(sql);
	    					//if最后一级 放到map里 key是最后一级的id value是platforminfo
	    					if(functionname.toUpperCase().equals("LAST") || functionname.toUpperCase().equals("CHANNELSONG"))
	    						lastChannelAndPlatform.put(tch_id, platforminfo);
	    					//不是 递归查询
	    					else
	    						buildLastChannelAndPlatform(tch_id,platforminfo,lastChannelAndPlatform);
	    				}
	    			}
	    			result.add(lastChannelAndPlatform);
	    		}
    		}
		} catch (Exception e) {
			log.info(" java bean getChannelPlatform fail !!!" +e.getMessage());
			e.printStackTrace();
		}
    	return result;
    }
    private void buildLastChannelAndPlatform(String tch_id,String platforminfo,DataMap lastChannelAndPlatform)
    {
    	String sql = "SELECT channel_id,function_name FROM t_channels where previous_id='"+tch_id+"'";
    	try {
			ArrayList chList = queryALLResultSet(sql);
			for(int i=0;i<chList.size();i++)
			{
				HashMap hashMap = (HashMap)chList.get(i);
				String fnName = (String)hashMap.get("function_name");
				String channel_id = (String)hashMap.get("channel_id");
				if(fnName.toUpperCase().equals("LAST") || fnName.toUpperCase().equals("CHANNELSONG"))
					lastChannelAndPlatform.put(channel_id, platforminfo);
				else
					buildLastChannelAndPlatform(channel_id,platforminfo,lastChannelAndPlatform);
			}
		} catch (Exception e) {
			log.info(" java bean buildLastChannelAndPlatform fail !!!" +e.getMessage());
			e.printStackTrace();
		}
    }
}
